package com.yutel.phone.binh.data.database;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.yutel.phone.binh.BuildConfig;
import com.yutel.phone.binh.data.vo.Category;
import com.yutel.phone.binh.data.vo.Channel;
import com.yutel.phone.binh.data.vo.Dict;

public class DBQueryHelper extends SQLiteOpenHelper {
	private static final String TAG = "ServiceDbHelper";
	private static final String dbName = "binh.db";// 数据库名称
	private static int version = 1;// 数据版本号

	public DBQueryHelper(Context context) {
		super(context, dbName, null, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.beginTransaction();
		try {
			db.execSQL(SQLManager.createChannelCategory);// 一级菜单表
			db.execSQL(SQLManager.createDict);// 一级菜单表
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage(), e);
		} finally {
			db.endTransaction();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.d(TAG, "Upgrade:oldVersion=" + oldVersion + ",=newVersion"
				+ newVersion);
		db.beginTransaction();
		try {
			db.execSQL(SQLManager.dropChannelCategory);
			db.execSQL(SQLManager.dropDict);
			db.execSQL(SQLManager.createChannelCategory);
			db.execSQL(SQLManager.createDict);
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.e(TAG, e.getMessage(), e);
		} finally {
			db.endTransaction();
		}
	}

	/**
	 * 初始化频道与节目
	 * 
	 * @param list
	 */
	public boolean initChannelCategory(ArrayList<Channel> list) {
		String sqlDel = "delete from channel_category";
		String sqlChannel = "insert into channel_category(id,icon,name,type,filter) values(?,?,?,?,?)";
		String sqlCategory = "insert into channel_category(id,pid,name,program) values(?,?,?,?)";
		SQLiteDatabase db = null;
		try {
			db = getWritableDatabase();// 获取数据对象
			db.beginTransaction();
			if (BuildConfig.DEBUG)
				if (list != null && list.size() > 0) {
					db.execSQL(sqlDel);
					for (Channel channel : list) {
						db.execSQL(sqlChannel, new Object[] { channel.getId(),
								channel.getIcon_url(), channel.getName(),
								"channel", channel.getFilter() });
						for (Category category : channel.getCategoryList()) {
							db.execSQL(
									sqlCategory,
									new Object[] { category.getId(),
											channel.getId(),
											category.getName(),
											category.getProgram() });
						}
					}
				}
			db.setTransactionSuccessful();
			return true;
		} catch (Exception e) {
			Log.e(TAG, "channel():", e);
			return false;
		} finally {
			if (db != null) {
				db.endTransaction();
				db.close();
			}
		}
	}

	public boolean initDict(ArrayList<Dict> list) {
		String sqlDel = "delete from Dict";
		String sql = "insert into Dict(num,type,value) values(?,?,?)";
		SQLiteDatabase db = null;
		try {
			db = getWritableDatabase();// 获取数据对象
			db.beginTransaction();
			if (list != null && list.size() > 0) {
				db.execSQL(sqlDel);
				for (Dict dict : list) {
					db.execSQL(
							sql,
							new Object[] { dict.getNum(), dict.getType(),
									dict.getValue() });
				}
			}
			db.setTransactionSuccessful();
			return true;
		} catch (Exception e) {
			Log.e(TAG, "channel():", e);
			return false;
		} finally {
			if (db != null) {
				db.endTransaction();
				db.close();
			}
		}
	}

	/**
	 * 获取频道
	 * 
	 * @return
	 */
	public ArrayList<Channel> getChannel() {
		String sqlChannel = "select id,icon,name,filter from channel_category where type=?";
		ArrayList<Channel> channels = new ArrayList<Channel>();
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = getReadableDatabase();// 获取数据对象
			cursor = db.rawQuery(sqlChannel, new String[] { "channel" });
			while (cursor.moveToNext()) {
				Channel channel = new Channel();
				channel.setId(cursor.getString(0));
				channel.setIcon_url(cursor.getString(1));
				channel.setName(cursor.getString(2));
				channel.setFilter(cursor.getString(3));
				channels.add(channel);
			}
			if (BuildConfig.DEBUG)
				Log.d(TAG, "list.size=" + channels.size());
		} catch (Exception e) {
			Log.e(TAG, "getChannel():", e);
		} finally {
			closeDB(db, cursor);
		}
		return channels;
	}

	/**
	 * 获取频道下的节目
	 * 
	 * @return
	 */
	public ArrayList<Category> getCategory(String pid) {
		String sqlCategory = "select id,name,program from channel_category where pid=?";
		ArrayList<Category> categorys = new ArrayList<Category>();
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = getReadableDatabase();// 获取数据对象
			cursor = db.rawQuery(sqlCategory, new String[] { pid });
			while (cursor.moveToNext()) {
				Category category = new Category();
				category.setId(cursor.getString(0));
				category.setName(cursor.getString(1));
				category.setProgram(cursor.getString(2));
				categorys.add(category);
			}
		} catch (Exception e) {
			Log.e(TAG, "getCategory():", e);
		} finally {
			closeDB(db, cursor);
		}
		return categorys;
	}

	/**
	 * 查询类型
	 * 
	 * @param num
	 * @param type
	 * @return
	 */
	public Dict getDict(Dict d) {
		ArrayList<String> arg = new ArrayList<String>();
		StringBuffer sql = new StringBuffer(
				"select num,type,value from dict where 1=1");
		if (d.getNum() > 0) {
			sql.append(" and num=?");
			arg.add(d.getNum() + "");
		}
		if (d.getType() > 0) {
			sql.append(" and type=?");
			arg.add(d.getType() + "");
		}
		if (d.getValue() != null && !"".equals(d.getValue())) {
			sql.append(" and value=?");
			arg.add(d.getValue());
		}
		Dict dict = null;
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = getReadableDatabase();
			cursor = db.rawQuery(sql.toString(),
					arg.toArray(new String[arg.size()]));
			if (cursor.moveToNext()) {
				dict = new Dict();
				dict.setNum(cursor.getInt(0));
				dict.setType(cursor.getInt(1));
				dict.setValue(cursor.getString(2));
			}
		} catch (Exception e) {
			Log.e(TAG, "getCategory():", e);
		} finally {
			closeDB(db, cursor);
		}
		return dict;
	}

	/**
	 * 关闭数据库
	 * 
	 * @param sqldb
	 * @param cursor
	 */
	private void closeDB(SQLiteDatabase sqldb, Cursor cursor) {
		if (cursor != null) {
			cursor.close();
		}
		if (sqldb != null) {
			sqldb.close();
		}
	}
}
